//
//  TrainQuery.m
//  TrainT1
//  $Id: TrainQuery.m 43 2010-06-07 08:49:24Z qiaolun $
//
//  Created by qiaolun on 6/3/10.
//  Copyright 2010 __MyCompanyName__. All rights reserved.
//

#import "TrainQuery.h"


@implementation TrainQuery

- (id) init {
	
	self = [super init];
	if(self){
		
		NSString *dbFile = [[NSBundle mainBundle] pathForResource:@"train" ofType:@"sqlite"];
		if(dbFile != nil){
			
			int rc;
			NSLog(@"open db %s", [dbFile UTF8String]);
			rc = sqlite3_open([dbFile UTF8String], &db);
			if(rc){
				NSLog(@"can't open database: %s", sqlite3_errmsg(db));
				sqlite3_close(db);
			}
			
		} else {
			NSLog(@"db file not found.");
		}
		
		
	}
	
	return self;
}

- (NSArray *) trainsFrom:(NSString *)fromStationName to:(NSString *)toStationName {
	NSString *sql = [NSString stringWithFormat:@"SELECT DISTINCT id FROM train WHERE stop_name = ? AND id IN ( SELECT id FROM train WHERE stop_name = ? )"];
	NSMutableArray *stops = [NSMutableArray arrayWithCapacity:10];
	
	int rc;
	// int col;
	int cols;
	
	
	sqlite3_stmt *stmt;
	
	rc = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, 0);
	if(rc != SQLITE_OK){
		NSLog(@"SQL Error: %s", sqlite3_errmsg(db));
		return nil;
	}
	
	rc = sqlite3_bind_text(stmt, 1, [fromStationName UTF8String], -1, SQLITE_STATIC);
	if(rc != SQLITE_OK){
		NSLog(@"SQL Error 1: %s", sqlite3_errmsg(db));
		return nil;
	}
	rc = sqlite3_bind_text(stmt, 2, [toStationName UTF8String], -1, SQLITE_STATIC);
	if(rc != SQLITE_OK){
		NSLog(@"SQL Error 2: %s", sqlite3_errmsg(db));
		return nil;
	}
	
	cols = sqlite3_column_count(stmt);
	// exec
	do {
		rc = sqlite3_step(stmt);
		switch(rc){
			case SQLITE_DONE:
				break;
				
			case SQLITE_ROW:
			{
				const char *txt = (const char*)sqlite3_column_text(stmt, 0);
				[stops addObject:[NSString stringWithUTF8String:txt]];
			}	
				break;
				
			default:
				NSLog(@"Error: %d : %s", rc, sqlite3_errmsg(db));
				break;
		}
		
	} while( rc == SQLITE_ROW );
	sqlite3_finalize(stmt);
	
	NSLog(@"got ids: %@", stops);
	
	return [NSArray arrayWithArray:stops];
}

- (NSArray *) trainsAtStation:(NSString *)stationName {
	
	NSString *sql = [NSString stringWithFormat:@"select distinct id from train where stop_name = ?"];
	NSMutableArray *stops = [NSMutableArray arrayWithCapacity:10];
	
	int rc;
	// int col;
	int cols;
	
	
	sqlite3_stmt *stmt;
	
	rc = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, 0);
	if(rc != SQLITE_OK){
		NSLog(@"SQL Error: %s", sqlite3_errmsg(db));
		return nil;
	}
	
	rc = sqlite3_bind_text(stmt, 1, [stationName UTF8String], -1, SQLITE_STATIC);
	if(rc != SQLITE_OK){
		NSLog(@"SQL Error: %s", sqlite3_errmsg(db));
		return nil;
	}
	
	cols = sqlite3_column_count(stmt);
	// exec
	do {
		rc = sqlite3_step(stmt);
		switch(rc){
			case SQLITE_DONE:
				break;
				
			case SQLITE_ROW:
			{
				const char *txt = (const char*)sqlite3_column_text(stmt, 0);
				[stops addObject:[NSString stringWithUTF8String:txt]];
			}	
				break;
				
			default:
				NSLog(@"Error: %d : %s", rc, sqlite3_errmsg(db));
				break;
		}
		
	} while( rc == SQLITE_ROW );
	sqlite3_finalize(stmt);
	
	NSLog(@"got ids: %@", stops);
	
	return [NSArray arrayWithArray:stops];
}


- (NSString *) trainCode:(NSString *)trainCode {
	
	NSString *sql = [NSString stringWithFormat:@"select distinct id from train where train_code = ?"];
	NSString *train_id = nil;
	
	int rc;
	// int col;
	int cols;
	
	
	sqlite3_stmt *stmt;
	
	rc = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, 0);
	if(rc != SQLITE_OK){
		NSLog(@"SQL Error: %s", sqlite3_errmsg(db));
		return nil;
	}
	
	rc = sqlite3_bind_text(stmt, 1, [trainCode UTF8String], -1, SQLITE_STATIC);
	if(rc != SQLITE_OK){
		NSLog(@"SQL Error: %s", sqlite3_errmsg(db));
		return nil;
	}
	
	cols = sqlite3_column_count(stmt);
	// exec
	do {
		rc = sqlite3_step(stmt);
		switch(rc){
			case SQLITE_DONE:
				break;
				
			case SQLITE_ROW:
			{
				const char *txt = (const char*)sqlite3_column_text(stmt, 0);
				train_id = [NSString stringWithUTF8String:txt];
			}	
				break;
				
			default:
				NSLog(@"Error: %d : %s", rc, sqlite3_errmsg(db));
				break;
		}
		
	} while( rc == SQLITE_ROW );
	sqlite3_finalize(stmt);
	
	NSLog(@"got id: %@", train_id);
	
	return train_id;
}

- (NSArray *) data:(NSString *)trainId {
	
	// only 1 record
	NSMutableArray *data = [NSMutableArray arrayWithCapacity:20];
	
	NSString *sql = [NSString stringWithFormat:@"select * from train where id = ? order by stop_num asc"];
	
	int rc;
	int col;
	int cols;
	
	
	sqlite3_stmt *stmt;
	
	rc = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, 0);
	if(rc != SQLITE_OK){
		NSLog(@"SQL Error: %s", sqlite3_errmsg(db));
		return nil;
	}
	
	rc = sqlite3_bind_text(stmt, 1, [trainId UTF8String], -1, SQLITE_STATIC);
	if(rc != SQLITE_OK){
		NSLog(@"SQL Error: %s", sqlite3_errmsg(db));
		return nil;
	}
	
	cols = sqlite3_column_count(stmt);
	// exec
	do {
		rc = sqlite3_step(stmt);
		switch(rc){
			case SQLITE_DONE:
				break;
				
			case SQLITE_ROW:
			{
				NSMutableDictionary *oneRow = [NSMutableDictionary dictionaryWithCapacity:cols];
				for(col=0; col<cols; col++){
					const char *txt = (const char*)sqlite3_column_text(stmt, col);
					const char *colname = sqlite3_column_name(stmt, col);
					[oneRow setObject:[NSString stringWithUTF8String:txt] forKey:[NSString stringWithUTF8String:colname]];
				}
				[data addObject:oneRow];
			}	
				break;
				
			default:
				NSLog(@"Error: %d : %s", rc, sqlite3_errmsg(db));
				break;
		}
		
	} while( rc == SQLITE_ROW );
	sqlite3_finalize(stmt);
	
	// NSLog(@"got data: %@", data);
	
	if([data count] > 0){
		return [NSArray arrayWithArray:data];
	} else {
		return nil;
	}
}

- (NSArray *) data:(NSString *)trainId fromStationName:(NSString *)from toStationName:(NSString *)to {
	return nil;
}


- (NSArray *) queryAllStations {
	
	NSMutableArray *data = [NSMutableArray arrayWithCapacity:100];
	
	NSString *sql = [NSString stringWithFormat:@"select distinct stop_name from train order by stop_name asc"];
	
	int rc;
	// int col;
	int cols;
	
	
	sqlite3_stmt *stmt;
	
	rc = sqlite3_prepare_v2(db, [sql UTF8String], -1, &stmt, 0);
	if(rc != SQLITE_OK){
		NSLog(@"SQL Error: %s", sqlite3_errmsg(db));
		return nil;
	}
	
	cols = sqlite3_column_count(stmt);
	// exec
	do {
		rc = sqlite3_step(stmt);
		switch(rc){
			case SQLITE_DONE:
				break;
				
			case SQLITE_ROW:
			{
				const char *txt = (const char*)sqlite3_column_text(stmt, 0);
				[data addObject:[NSString stringWithUTF8String:txt]];
			}	
				break;
				
			default:
				NSLog(@"Error: %d : %s", rc, sqlite3_errmsg(db));
				break;
		}
		
	} while( rc == SQLITE_ROW );
	sqlite3_finalize(stmt);
	
	// NSLog(@"got data: %@", data);
	
	if([data count] > 0){
		return [NSArray arrayWithArray:data];
	} else {
		return nil;
	}
}

- (NSArray *) queryAllTrains {
	return nil;
}


- (void) dealloc {
	
	sqlite3_close(db);
	
	[super dealloc];
}

@end
